package com.silknet.db;

import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.StringReader;
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Set;
import java.util.TreeMap;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.sax.SAXSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;

import org.xml.sax.InputSource;

import com.silknet.images.ImageStore;
import com.silknet.images.Object_type;

public class DBOperations {
	// public static TreeMap<Integer, ArrayList<Object[]>> getObjectNames(
	// Connection connn) throws Exception {
	// TreeMap<Integer, ArrayList<Object[]>> result = new TreeMap<Integer,
	// ArrayList<Object[]>>();
	// StringBuffer SQL = new StringBuffer();
	// SQL.append("select object_type, trim(object_name) object_name,object_id from object_names ot ");
	//
	// Connection con = null;
	// PreparedStatement stmt = null;
	// ResultSet rs = null;
	// System.out.println(SQL.toString());
	// try {
	// con = connn == null ? DBConnection.getConnection("FlyComp") : connn;
	// stmt = con.prepareStatement(SQL.toString());
	// rs = stmt.executeQuery();
	// while (rs.next()) {
	// int type = rs.getInt("object_type");
	// ArrayList<Object[]> list = result.get(type);
	// if (list == null) {
	// list = new ArrayList<Object[]>();
	// result.put(type, list);
	// }
	// list.add(new Object[] { rs.getString("object_name"),
	// rs.getInt("object_id") });
	// }
	// } catch (Exception e) {
	// e.printStackTrace();
	// throw new Exception(e.getMessage());
	//
	// } finally {
	// try {
	// rs.close();
	// } catch (Exception e2) {
	// }
	// try {
	// stmt.close();
	// } catch (Exception e2) {
	// }
	// try {
	// if (connn == null)
	// con.close();
	// } catch (Exception e2) {
	// }
	// }
	// return result;
	// }

	public static TreeMap<Integer, ArrayList<Object[]>> getObjectNames(
			Connection connn) throws Exception {
		TreeMap<Integer, ArrayList<Object[]>> result = new TreeMap<Integer, ArrayList<Object[]>>();
		StringBuffer SQL = new StringBuffer();
		SQL.append("select object_type, trim(object_name) object_name,object_id from object_names ot ");

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		System.out.println(SQL.toString());
		try {
			con = connn == null ? DBConnection.getConnection("FlyComp") : connn;
			stmt = con.prepareStatement(SQL.toString());
			rs = stmt.executeQuery();
			while (rs.next()) {
				int type = rs.getInt("object_type");
				ArrayList<Object[]> list = result.get(type);
				if (list == null) {
					list = new ArrayList<Object[]>();
					result.put(type, list);
				}
				list.add(new Object[] { rs.getString("object_name"),
						rs.getInt("object_id") });
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());

		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
			}
			try {
				stmt.close();
			} catch (Exception e2) {
			}
			try {
				if (connn == null)
					con.close();
			} catch (Exception e2) {
			}
		}
		return result;
	}

	public static TreeMap<Integer, Object_type> getObjectTypes(
			Connection connn, HttpServletRequest request, ServletContext context)
			throws Exception {

		TreeMap<Integer, Object_type> mapResult = new TreeMap<Integer, Object_type>();
		TreeMap<Integer, Object_type> result = new TreeMap<Integer, Object_type>();
		StringBuffer SQL = new StringBuffer();
		SQL.append("select objt_id, type_name,parent_id,image_name from object_type  ot where image_name is not null or exists(select 1 from object_type  ot1 where ot1.parent_id = ot.objt_id and ot1.image_name is not null) order by type_name");

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		System.out.println(SQL.toString());
		try {
			con = connn == null ? DBConnection.getConnection("FlyComp") : connn;
			stmt = con.prepareStatement(SQL.toString());

			rs = stmt.executeQuery();
			while (rs.next()) {
				Object_type r = new Object_type();
				r.setObjt_id(rs.getInt("objt_id"));
				r.setType_name(rs.getString("type_name"));
				r.setParent_id(rs.getInt("parent_id"));
				r.setImage_name(rs.getString("image_name"));
				if (request != null && r.getImage_name() != null) {
					ImageStore.addImageData(r.getObjt_id(), r.getImage_name(),
							request, context);
				}
				mapResult.put(r.getObjt_id(), r);
			}

		} catch (Exception e) {
			e.printStackTrace();
			throw new Exception(e.getMessage());

		} finally {
			try {
				rs.close();
			} catch (Exception e2) {
			}
			try {
				stmt.close();
			} catch (Exception e2) {
			}
			try {
				if (connn == null)
					con.close();
			} catch (Exception e2) {
			}
		}
		return mapResult;
	}

	// public static TreeMap<Integer, String> getObjectTypes(int type,
	// Connection connn) throws Exception {
	// TreeMap<Integer, String> result = new TreeMap<Integer, String>();
	// StringBuffer SQL = new StringBuffer();
	// SQL.append("select typeid, typename from classificator where cltype=?");
	//
	// Connection con = null;
	// PreparedStatement stmt = null;
	// ResultSet rs = null;
	// System.out.println(SQL.toString());
	// try {
	// con = connn == null ? DBConnection.getConnection("FlyComp") : connn;
	// stmt = con.prepareStatement(SQL.toString());
	// stmt.setInt(1, type);
	// rs = stmt.executeQuery();
	// while (rs.next()) {
	// result.put(rs.getInt("typeid"), rs.getString("typename"));
	// }
	// } catch (Exception e) {
	// e.printStackTrace();
	// throw new Exception(e.getMessage());
	//
	// } finally {
	// try {
	// rs.close();
	// } catch (Exception e2) {
	// }
	// try {
	// stmt.close();
	// } catch (Exception e2) {
	// }
	// try {
	// if (connn == null)
	// con.close();
	// } catch (Exception e2) {
	// }
	// }
	// return result;
	// }

	// public static String getMenu(HttpServletRequest request,
	// ServletContext context) throws Exception {
	// Connection con = null;
	// try {
	// con = DBConnection.getConnection("FlyComp");
	// TreeMap<Integer, Object_type> objectTypes = getObjectTypes(con,
	// request, context);
	// // TreeMap<Integer, ArrayList<Object[]>> objectNames =
	// // getObjectNames(con);
	// StringBuffer sb = new StringBuffer(
	// "<ul class=\"tree\" style=\"margin-left: 15px;\">");
	// Collection<Object_type> values = objectTypes.values();
	// List<Object_type> list = new ArrayList<Object_type>(values);
	// Collections.sort(list, new Comparator<Object_type>() {
	// public int compare(Object_type o1, Object_type o2) {
	// return o1.getType_name().compareTo(o2.getType_name());
	// }
	// });
	// for (Object_type value : list) {
	// value.generateMenu(sb, "\t");
	// }
	// sb.append("</ul>");
	// return sb.toString();
	// } catch (Exception e) {
	// e.printStackTrace();
	// throw new Exception(e.getMessage());
	//
	// } finally {
	//
	// try {
	// con.close();
	// } catch (Exception e2) {
	// }
	// }
	// }

	// public static String getMenu() throws Exception {
	// Connection con = null;
	// try {
	// con = DBConnection.getConnection("FlyComp");
	// TreeMap<Integer, String> objectTypes = getObjectTypes(2, con);
	// TreeMap<Integer, ArrayList<Object[]>> objectNames = getObjectNames(con);
	// StringBuffer sb = new StringBuffer(
	// "<ul class=\"tree\" style=\"margin-left: 15px;\">");
	// Set<Integer> keys = objectTypes.keySet();
	// for (Integer key : keys) {
	// ArrayList<Object[]> list = objectNames.get(key);
	//
	// if (list == null)
	// continue;
	// sb.append("\t<li>\n\t\t<input type=\"checkbox\">\n\t\t<label><b>"
	// + convertUTF(objectTypes.get(key))
	// + "</b></label>\n\t\t<ul>");
	// int index = 0;
	// for (Object[] o : list) {
	// String cbn = "cb" + key + "_" + index++;
	// String objectName = o[0].toString();
	// String val = convertUTF(objectName);
	// sb.append("\n\t\t\t<li>\n\t\t\t\t<input type=\"checkbox\" name=\""
	// + cbn
	// + "\" type_id=\""
	// + o[1].toString()
	// + "\" value=\"" + cbn + "\">");
	// sb.append("\n\t\t\t\t<label>" + val + "</label>");
	// sb.append("\n\t\t\t</li>");
	// }
	// sb.append("\n\t\t</ul>");
	//
	// }
	// sb.append("</ul>");
	// return sb.toString();
	// } catch (Exception e) {
	// e.printStackTrace();
	// throw new Exception(e.getMessage());
	//
	// } finally {
	//
	// try {
	//
	// con.close();
	// } catch (Exception e2) {
	// }
	// }
	// }

	// public static String getSpacialMapPoints(int user_id, String criteria,
	// int srid) throws Exception {
	// StringBuffer sb = new StringBuffer();
	// String sql = "select img_non_selected_id imageid"
	// +
	// "\n,array_to_string(array_agg(di.id||';'||X(transform(di.the_geom,?))||';'||Y(transform(di.the_geom,?))),'-') points"
	// + "\nfrom  datainput di  inner join styler s on s.id=di.style_id "
	// + "\n where 1=1 %s" + " \n group by img_non_selected_id";
	// String addjoin = "";
	// if (criteria == null || criteria.trim().length() == 0)
	// addjoin = "and 1!=1";
	// else {
	// // if (!criteria.trim().toLowerCase().startsWith("where"))
	// // criteria = " where (" + criteria.trim() + ")";
	// BASE64Decoder decoder = new BASE64Decoder();
	// byte[] decodedBytes = decoder.decodeBuffer(criteria);
	// criteria = new String(decodedBytes, "UTF-8");
	// addjoin =
	// " and (di.object_type, trim(di.object_name)) in (select object_type, trim(object_name) from object_names otn where otn.object_id in (";
	// addjoin += criteria + "-1))";
	// }
	// sql = String.format(sql, addjoin);
	// Connection con = null;
	// PreparedStatement stmt = null;
	// ResultSet rs = null;
	// try {
	// con = DBConnection.getConnection("FlyComp");
	// stmt = con.prepareStatement(sql);
	// stmt.setInt(1, srid);
	// stmt.setInt(2, srid);
	// rs = stmt.executeQuery();
	// while (rs.next()) {
	// String s = rs.getInt("imageid") + "#" + rs.getString("points");
	// sb.append(s + "\n");
	// }
	// } catch (Exception e) {
	// throw new Exception(e.getMessage());
	// } finally {
	// try {
	// rs.close();
	// } catch (Exception e2) {
	//
	// }
	// try {
	// stmt.close();
	// } catch (Exception e2) {
	//
	// }
	// try {
	// con.close();
	// } catch (Exception e2) {
	//
	// }
	// }
	// return sb.toString();
	// }

	public static String getSpacialMapStreetsAndBuildings(int srid,
			String street_name, String buildNo, Connection conn)
			throws Exception {
		StringBuffer sb = new StringBuffer();
		street_name = street_name == null ? "" : street_name.trim();
		buildNo = buildNo == null ? "" : buildNo.trim();
		if (!street_name.isEmpty()) {
			sb.append("select '1;'||astext(transform(ST_Collect(gs.the_geom),?)) geomastext  from \n");
			sb.append("streetnames st inner join gstreets gs on gs.r_street_id =st.id where gs.the_geom is not null and lower(streetname) like '%'||?||'%'\n");

			if (!buildNo.isEmpty()) {
				sb.append("union all \n");
				sb.append("select '2;'||astext(transform(ST_Collect(b.the_geom),?)) geomastext  from \n");
				sb.append("mbuildings b where the_geom is not null  and bnumber like '%'||?||'%' and \n");
				sb.append("exists (select 1 from streetnames st where lower(streetname) like '%'||?||'%' and (b.streeti_d1=st.id or b.streeti_d2=st.id))");
			}
		}

		String sql = sb.toString();
		if (sql.trim().isEmpty())
			return "";
		sb = new StringBuffer();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? DBConnection.getConnection("FlyComp") : conn;
			stmt = con.prepareStatement(sql);
			int pindex = 1;
			stmt.setInt(pindex++, srid);
			stmt.setString(pindex++, street_name);
			if (!buildNo.isEmpty()) {
				stmt.setInt(pindex++, srid);
				stmt.setString(pindex++, buildNo);
				stmt.setString(pindex++, street_name);
			}
			rs = stmt.executeQuery();
			while (rs.next()) {
				String s = rs.getString("geomastext");
				sb.append(s + "\n");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {

			}
			try {
				stmt.close();
			} catch (Exception e2) {

			}
			if (conn == null)
				try {
					con.close();
				} catch (Exception e2) {

				}
		}
		return sb.toString();
	}

	public static String getSpacialMapPoints(String criteria,
			String object_name, int srid, String street_name, String buildNo,
			Connection conn, String object_type) throws Exception {
		StringBuffer sb = new StringBuffer();
		if (object_name == null)
			object_name = "";
		object_name = object_name.trim().toLowerCase();
		String sql = "select object_type imageid"
				+ "\n,array_to_string(array_agg(di.id||';'||X(transform(di.the_geom,?))||';'||Y(transform(di.the_geom,?))),'-') points"
				+ "\nfrom  tbilisi_pois di " + "\n where 1=1 %s ";
		String addjoin = "";
		object_type = object_type == null ? "" : object_type.trim();
		if (!object_type.isEmpty() && !object_type.equals("-1"))
			object_type = " and object_type=" + object_type;
		if (criteria == null || criteria.trim().length() == 0) {
			if (object_type.isEmpty() && object_name.isEmpty())
				addjoin = "and 1!=1";
		} else {
			// if (!criteria.trim().toLowerCase().startsWith("where"))
			// // criteria = " where (" + criteria.trim() + ")";
			// BASE64Decoder decoder = new BASE64Decoder();
			// byte[] decodedBytes = decoder.decodeBuffer(criteria);
			// criteria = new String(decodedBytes, "UTF-8");
			addjoin = " and object_type in (";
			addjoin += criteria + "-1)";
		}

		if (street_name != null) {
			street_name = street_name.trim();
			if (!street_name.isEmpty()) {
				addjoin += " and exists (select 1 from streetnames st where lower(streetname) like '%"
						+ street_name + "%' and (di.street_id=st.id))";
				buildNo = buildNo == null ? "" : buildNo.trim();
				if (!buildNo.isEmpty()) {
					addjoin += " and lower(street_no) like '%" + buildNo + "%'";
				}
			}

		}

		sql = String.format(sql, object_type + addjoin);
		sql += " and lower(poi_name) like '%'||?||'%'"
				+ " \n group by object_type ";
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? DBConnection.getConnection("FlyComp") : conn;
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, srid);
			stmt.setInt(2, srid);
			stmt.setString(3, object_name);
			rs = stmt.executeQuery();
			while (rs.next()) {
				String s = rs.getInt("imageid") + "#" + rs.getString("points");
				sb.append(s + "\n");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {

			}
			try {
				stmt.close();
			} catch (Exception e2) {

			}
			if (conn == null)
				try {
					con.close();
				} catch (Exception e2) {

				}
		}
		return sb.toString();
	}

	public static String getSpacialMapPointInfo(int id) throws Exception {
		StringBuffer result = new StringBuffer();
		String sql = "select poi_name"
				+ "\nfrom  tbilisi_pois di \n where id=?";

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = DBConnection.getConnection("FlyComp");
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			while (rs.next()) {
				String s = Object_type.convertUTF(rs.getString("poi_name"));
				result.append(s);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {

			}
			try {
				stmt.close();
			} catch (Exception e2) {

			}
			try {
				con.close();
			} catch (Exception e2) {

			}
		}
		return result.toString();
	}

	public static String[] getTemplateObjectTypeID(int id) throws Exception {
		return getTemplateObjectTypeID(id,
				DBConnection.getConnection("FlyComp"));
	}

	public static String[] getTemplateObjectTypeID(int id, Connection conn)
			throws Exception {
		String[] result = new String[2];
		String sql = "select select_method,xslt_style"
				+ "\nfrom  v_object_type di \n where objt_id=?";

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? DBConnection.getConnection("FlyComp") : conn;
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				result[0] = rs.getString("select_method");
				result[1] = rs.getString("xslt_style");
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {

			}
			try {
				stmt.close();
			} catch (Exception e2) {

			}
			if (conn == null)
				try {
					con.close();
				} catch (Exception e2) {

				}
		}
		return result;
	}

	public static String getObjectByTemplate(int id, String sql, Connection conn)
			throws Exception {
		String result = "";

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = conn == null ? DBConnection.getConnection("FlyComp") : conn;
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, id);
			rs = stmt.executeQuery();
			if (rs.next()) {
				result = rs.getString(1);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {

			}
			try {
				stmt.close();
			} catch (Exception e2) {

			}
			if (conn == null)
				try {
					con.close();
				} catch (Exception e2) {

				}
		}
		return result;
	}

	public static ArrayList<String> getSpacialMapPointNames(String query,
			int object_type) throws Exception {
		ArrayList<String> result = new ArrayList<String>();
		String sql = "select distinct poi_name"
				+ "\nfrom  tbilisi_pois di \n where (?<=0 or object_type=?) and  lower(poi_name) like '%'||?||'%' limit 30";

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = DBConnection.getConnection("FlyComp");
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, object_type);
			stmt.setInt(2, object_type);
			stmt.setString(3, query.trim().toLowerCase());
			rs = stmt.executeQuery();
			while (rs.next()) {
				String s = (rs.getString("poi_name"));
				result.add(s);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {

			}
			try {
				stmt.close();
			} catch (Exception e2) {

			}
			try {
				con.close();
			} catch (Exception e2) {

			}
		}
		return result;
	}

	public static ArrayList<String> getStreetNames(String query)
			throws Exception {
		ArrayList<String> result = new ArrayList<String>();
		String sql = "select distinct streetname"
				+ "\nfrom  streetnames di \n where lower(streetname) like '%'||?||'%' order by 1 limit 30 ";

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = DBConnection.getConnection("FlyComp");
			stmt = con.prepareStatement(sql);
			stmt.setString(1, query.trim().toLowerCase());
			rs = stmt.executeQuery();
			while (rs.next()) {
				String s = (rs.getString("streetname"));
				result.add(s);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				rs.close();
			} catch (Exception e2) {

			}
			try {
				stmt.close();
			} catch (Exception e2) {

			}
			try {
				con.close();
			} catch (Exception e2) {

			}
		}
		return result;
	}

	public static void main(String[] args) throws Exception {
		Connection con = DriverManager.getConnection(
				"jdbc:postgresql://127.0.0.1:5432/osm", "bfuser", "bfuser");
//		checkImages();
		getSpacialMapStreetsAndBuildings(900913, "ჭავჭ", null, con);
		con.close();
		// object_XML = "<?xml version=\"1.0\"?><ROW>" + object_XML + "</ROW>";
		// System.out.println(object_XML);
		// TransformerFactory tf = TransformerFactory.newInstance();
		// SAXSource ss = new SAXSource(new InputSource(new StringReader(
		// templates[1])));
		//
		// Transformer t = tf.newTransformer(ss);
		// StringWriter sw = new StringWriter();
		// t.setParameter("img_url", "bb");
		// ss = new SAXSource(new InputSource(new StringReader(object_XML)));
		// t.transform(ss, new StreamResult(sw));
		// System.out.println(sw.getBuffer().toString());

	}

	private static void checkImages() throws SQLException, Exception {
		Connection con = DriverManager.getConnection(
				"jdbc:postgresql://127.0.0.1:5432/osm", "bfuser", "bfuser");
		TreeMap<Integer, Object_type> dat = getObjectTypes(con, null, null);
		con.close();
		Set<Integer> keys = dat.keySet();
		File dir = new File("C:\\icons");
		for (Integer key : keys) {
			Object_type t = dat.get(key);
			String image_name = t.getImage_name();
			if (image_name == null)
				continue;
			image_name = image_name.trim();
			if (image_name.isEmpty())
				continue;
			File icf = new File(dir, image_name);
			if (!icf.exists()) {
				System.out.println("Image for id " + t.getObjt_id()
						+ " and with image " + image_name
						+ " doesnot exists!!!");
			}
		}
		String[] files = dir.list();
		System.out.println("checking for assign");

		for (String file : files) {
			boolean assigned = false;
			for (Integer key : keys) {
				Object_type t = dat.get(key);
				String image_name = t.getImage_name();
				if (image_name == null)
					continue;
				image_name = image_name.trim();
				if (image_name.isEmpty())
					continue;
				if (file.equals(image_name)) {
					assigned = true;
					break;
				}
			}
			if (!assigned) {
				// System.out.println("Image with name " + file
				// + "  not assigned!!!");
				System.out.println(file);
			}
		}
	}
}
